const connection = require('../app/database')

// const commonStatement = `SELECT m.id id, m.content content, m.createAt createTime, m.updateAt updateTime,
// JSON_OBJECT('id', u.id, 'name', u.name) author
// from moment as m
// LEFT JOIN user u
// on u.id = m.user_id`
class MomentService {
  async publicMoment(userId, content) {
    const statement = `INSERT INTO moment (content, user_id) VALUES(?, ?);`
    const [result] = await connection.execute(statement, [content, userId])
    return result
  }
  // 一次性查询出动态的相关信息，以及该条动态所包含的评论列表
  async getMomentById(momentId) {
    const statement = `select m.id mid, m.content content, m.createAt createTime,
		JSON_OBJECT('id', u.id, 'name', u.name, 'avatarUrl', u.avatar_url) momentAuthor,
		IF(COUNT(l.id), JSON_ARRAYAGG(JSON_OBJECT('id', l.id, 'name', l.name)), NULL) labels,
	(
		SELECT IF(COUNT(c.id), JSON_ARRAYAGG(
			JSON_OBJECT('id', c.id, 'content', c.content, 'commentId', c.comment_id, 'createTime', c.createAt,
									'user', JSON_OBJECT('id', cu.id, 'name', cu.name, 'avatarUrl', cu.avatar_url))
		),NULL) FROM comment c LEFT JOIN user cu ON c.user_id = cu.id WHERE m.id = c.moment_id
	) comments,
  (SELECT JSON_ARRAYAGG(CONCAT('http://localhost:8000/moment/images/', file.filename)) 
        FROM file WHERE m.id = file.moment_id) images
    from moment m
    LEFT JOIN user u ON m.user_id = u.id
    LEFT JOIN moment_label ml ON m.id = ml.moment_id
    LEFT JOIN label l ON ml.label_id = l.id
    where m.id = ?
    GROUP BY m.id;`
    const [result] = await connection.execute(statement, [momentId])
    return result
  }
  async getMomentList(offset, size) {
    const statement = `SELECT m.id id, m.content content, m.createAt createTime, m.updateAt updateTime,
    JSON_OBJECT('id', u.id, 'name', u.name) author,
    (SELECT COUNT(*) FROM comment c WHERE c.moment_id = m.id) commentCount,
    (SELECT COUNT(*) FROM moment_label ml WHERE ml.moment_id = m.id) labelCount,
    (SELECT JSON_ARRAYAGG(CONCAT('http://localhost:8000/moment/images/', file.filename)) 
        FROM file WHERE m.id = file.moment_id) images
    from moment as m 
    LEFT JOIN user u
    on u.id = m.user_id
    limit ?, ?`
    const [result] = await connection.execute(statement, [offset, size])
    return result
  }
  async updateMoment(momentId, content) {
    const statement = `UPDATE moment SET content = ? WHERE id = ?;`
    const [result] = await connection.execute(statement, [content, momentId])
    return result
  }
  async deleteMoment(momentId) {
    const statement = `DELETE FROM moment WHERE id = ?;`
    const [result] = await connection.execute(statement, [momentId])
    return result
  }

  async hasLabel(momentId, labelId) {
    const statement = `SELECT * FROM moment_label WHERE moment_id = ? AND label_id = ?`
    const [result] = await connection.execute(statement, [momentId, labelId])
    return result.length
  }
  async addLabel(momentId, labelId) {
    const statement = `INSERT INTO moment_label (moment_id, label_id) VALUES (?, ?);`
    const [result] = await connection.execute(statement, [momentId, labelId])
    return result
  }
}

module.exports = new MomentService()
